#!/bin/bash
# File Name: analysis_binlog.sh
# Author: moshan
# mail: mo_shan@yeah.net
# Created Time: 2019-04-11 18:34:42
# Function: Analysis binlog for MySQL
#########################################################################
work_dir=/data/git/analysis_binlog
. ${work_dir}/function/logging.sh
localhost_ip="${5}"
log_file="${7}"
function f_analysis_binlog()
{
	mysqlbinlog="${1}"
	binlog_file="${2}"
	show="${3}"
	res_file="${4}"
	sort_pos="${5}"
	${mysqlbinlog}  --base64-output=decode-rows -vv ${binlog_file}|awk -v show_opt="${show}" -v sort_opt="${sort_pos}" 'BEGIN {
		table=1;
		trs_count=0;
		show_time="Last Time";
		show_table="Table";
		show_type="Type";
		show_count="affected(row)";
		db_name="";
		t_name="";
		show_total="Trans(total)";
		show_insert="Insert(s)";
		show_update="Update(s)";
		show_delete="Delete(s)";
		s_type=""; 
		s_count=0;
		count=0;
		start=0;
		insert_count=0;
		update_count=0;
		delete_count=0;
		flag=0;
	}
	{
	if (show_opt=="detail" && start==0)
		{
			printf "\033[35m%-60s%-20s%-15s%-15s%-15s%-15s%-15s%-15s\n\033[0m",show_table,show_time,show_type,show_count,show_insert,show_update,show_delete,show_total;
			start=1;
		}
		if(match($0, /^#.*server id.*Table_map:.*mapped to number/)) 
		{
			split($(NF-4),a,"`");
			db_name=a[2];
			t_name=a[4];
			t_name_tmp=(db_name"."t_name);
			flag=1;
			t_time=(substr($1,2,6)" "$2);
		}
		else if (match($0, /(### INSERT INTO .*\..*)/)) 
		{
			count=count+1;
			insert_count=insert_count+1;
			s_type="INSERT"; 
			s_count=s_count+1;
		}
		else if (match($0, /(### UPDATE .*\..*)/)) 
		{
			count=count+1;
			update_count=update_count+1;
			s_type="UPDATE"; 
			s_count=s_count+1;
		}
		else if (match($0, /(### DELETE FROM .*\..*)/)) 
		{
			count=count+1;
			delete_count=delete_count+1;
			s_type="DELETE"; 
			s_count=s_count+1;
		}
		else if (match($0, /^(# at) /) && flag==1 && s_count>0) 
		{
			opt_insert=(t_name_tmp"_insert");
			opt_update=(t_name_tmp"_update");
			opt_delete=(t_name_tmp"_delete");
			opt_time=(t_name_tmp"_time");
			if (table>1)
			{
				mark=0;
				for (table_tmp=1;table_tmp<=table;table_tmp++)
				{
					if (index(t_name_array[table_tmp],t_name_tmp))
					{
						mark=1;
						break;
					}
				}
				if(mark==0)
				{
					res[opt_insert]=0;
					res[opt_update]=0;
					res[opt_delete]=0;
					res[opt_time]="";
					t_name_array[table]=t_name_tmp;
					table++;
					mark=0;
				}
			}
			else if(table==1)
			{
				res[opt_insert]=0;
				res[opt_update]=0;
				res[opt_delete]=0;
				res[opt_time]=""
				t_name_array[1]=t_name_tmp;
				table++;
			}
			if (s_type=="DELETE")
			{
				res[opt_delete]+=s_count;
			}
			else if (s_type=="UPDATE")
			{
				res[opt_update]+=s_count;
			}
			else if (s_type=="INSERT")
			{
				res[opt_insert]+=s_count;
			}
			res[opt_time]=t_time;
			if (show_opt=="detail")
			{
				printf "\033[32m%-60s%-20s%-15s%-15s%-15s%-15s%-15s%-15s\n\033[0m",t_name_tmp,t_time,s_type,s_count,res[opt_insert],res[opt_update],res[opt_delete],trs_count;
			}
			s_type=""; 
			s_count=0; 
		}
		else if (match($0, /^(COMMIT)/)) 
		{
			trs_count+=1;
			count=0;
			insert_count=0;
			update_count=0; 
			delete_count=0;
			s_type=""; 
			s_count=0; 
			flag=0;
		}
	} END{
		t_name_array_length=length(t_name_array);
		printf "\033[35m%-60s%-30s%-15s%-15s%-15s\033[0m\n",show_table,show_time,show_insert,show_update,show_delete;
		sum_insert=0;
		sum_delete=0;
		sum_update=0;
	    if (sort_opt=="update")
		{
			sort_pos=5;
		}
	    else if (sort_opt=="delete")
		{
			sort_pos=6;
		}
	    else
		{
			sort_pos=4;
		}
		for (i=1;i<=t_name_array_length;i++)
		{
			opt_insert=(t_name_array[i]"_insert");
			opt_update=(t_name_array[i]"_update");
			opt_delete=(t_name_array[i]"_delete");
			opt_time=(t_name_array[i]"_time");
			sum_insert+=res[opt_insert];
			sum_delete+=res[opt_delete];
			sum_update+=res[opt_update];
			printf "\033[32m%-60s%-30s%-15s%-15s%-15s\n\033[0m",t_name_array[i],res[opt_time],res[opt_insert],res[opt_update],res[opt_delete]| "sort -rn -k"sort_pos;
		}
		print "";
		printf "\033[35m%-60s%-30s%-15s%-15s\033[0m\n",show_total,show_insert,show_update,show_delete;
		printf "\033[32m%-60s%-30s%-15s%-15s\n\033[0m",trs_count,sum_insert,sum_update,sum_delete;
	}' > ${res_file}
	[ $? -eq 0 ] && f_logging "WARN" "Analysis completed --> ${binlog_file}" "2"|tee -a ${log_file}  || f_logging "ERROR" "Analysis completed --> ${binlog_file}"|tee -a ${log_file} 
}
f_analysis_binlog "${1}" "${2}" "${3}" "${4}" "${6}"
